package cn.kgc.tangcco.tcbd1011.no13.user.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.kgc.tangcco.tcbd1011.no13.base.util.JDBCUtils;
import cn.kgc.tangcco.tcbd1011.no13.user.dao.UserDao;
import cn.kgc.tangcco.tcbd1011.no13.user.entity.User;

public class UserDaoImpl implements UserDao {

	private QueryRunner queryRunner;
	public UserDaoImpl() {
//		获得数据库连接
		queryRunner = new QueryRunner(JDBCUtils.getDataSource());
	}
//	将重复代码抽取成一个类
	public int executeUpdate(String sql,Object...objects) {
		try {
//			调用QueryRunner的update方法
			return queryRunner.update(sql, objects);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	
	
	@Override
	public List<User> queryAllUser(User user) {
		try {
//			通过sql语句执行查询所有用户的方法  
			return queryRunner.query("select * from user order by userid desc", new BeanListHandler<>(User.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
//	根据用户属性查询用户信息
	@Override
	public User detailUser(User user) {
		StringBuffer sb = new StringBuffer();
		List<Object> ulist = new ArrayList<>();
		String sql="select * from user where true";
		sb.append(sql);
		if(user.getUserName()!=null && !user.getUserName().isEmpty()) {
			sb.append(" and userName=?");
			ulist.add(user.getUserName());
		}
		if(user.getEmail()!=null && !user.getEmail().isEmpty()) {
			sb.append(" and email=?");
			ulist.add(user.getEmail());
		}
		if(user.getUserId()!=null && user.getUserId()>0) {
			sb.append(" and id=?");
			ulist.add(user.getUserId());
		}
		sql=sb.toString();
//		System.out.println(sql);
		try {
//			通过sql语句执行查询用户的方法 
			return queryRunner.query(sql, new BeanHandler<>(User.class), ulist.toArray());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public int queryCountUser() {
		try {
//			通过sql语句执行查询用户表数据数量的方法，返回结果是long类型需强转成int类型 
			return ((Long)queryRunner.query("select count(*) from user", new ScalarHandler<>())).intValue();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public int addUser(User user) {
//		通过sql语句执行添加用户的方法 
		return this.executeUpdate("insert into user values(default,?,?,default,?,?,?,now())", 
				user.getUserName(),user.getPassword(),user.getNickName(),user.getTel(),user.getEmail());
	}

	@Override
	public int updateUser(User user) {
//		通过sql语句执行修改用户的方法 
		return this.executeUpdate("update user set userName=?,passWord=?,userroles=?,nickname=?,tel=?,email=? where userId=?", 
				user.getUserName(),user.getPassword(),user.getUserRoles(),user.getNickName(),user.getTel(),user.getEmail(),user.getUserId());
	}

	@Override
	public int deleteUser(int pk) {
//		通过sql语句执行删除用户的方法 
		return this.executeUpdate("delete from user where userid=?", pk);
	}
	@Override
	public User login(User user) {
		try {
			return queryRunner.query("select * from user where userName=? and password=?",new BeanHandler<>(User.class),user.getUserName(),user.getPassword());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
//	重置密码，便于用户执行忘记密码操作	
	@Override
	public int updatePassord(User user) {
		return this.executeUpdate("UPDATE USER SET PASSWORD=? WHERE userName=?", user.getPassword(),user.getUserName());
	}

}
